package org.onejava.db.mapping;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.Map;

import org.onejava.db.DbcpConnection;

/**
 * SQL处理类，处理SQL并且返回map结果集
 * @author frost
 *
 */
public class SqlMapping extends BaseSqlMapping {
	
	/**
	 * 聚合函数查询
	 * @param sql 必须的是一个 聚合函数    max  min count
	 * @param map
	 * @return
	 * @throws Exception
	 */
	public  int   getPrimaryKey(String  sql,Map map)throws Exception{
		Map map2 = sqlAnalyze2(sql, map);
		return psPrimaryKey((String)map2.get("sql"),(List)map2.get("list"));
	}
	
	/**
	 * 聚合函数查询
	 * @param   必须的是一个 聚合函数    max  min count
	 */
	private  int   psPrimaryKey(String  sql,List params)throws Exception{
		if(TRANSACTION_ENTITY.get()==null){
			Connection myconn=DbcpConnection.getConnection();
			PreparedStatement myPs =null;
			ResultSet rs=null;
			int  result = 0;
			try {
				myconn.setAutoCommit(true);
				myPs = myconn.prepareStatement(sql);
				if(params != null && params.size() > 0){
					for (int i = 0; i < params.size(); i++) {
						myPs.setString(i+1,(String) params.get(i));
					}
				}
				rs = myPs.executeQuery();
				rs.next();
			    result = Integer.parseInt(rs.getString(1));
				return  result; 
			} catch(Exception e){
				log.debug(sql);
				throw new Exception(e);
			}finally{
				if(null!=rs){
					rs.close();
					myPs.close();
					myconn.close();
				}
			}	
		}else{
			PreparedStatement myPs = TRANSACTION_ENTITY.get().getConn().prepareStatement(sql);
			ResultSet rs=null;
			int  result = 0;
			try {
				if(params != null && params.size() > 0){
					for (int i = 0; i < params.size(); i++) {
						myPs.setString(i+1,(String) params.get(i));
					}
				}
				rs = myPs.executeQuery();
				rs.next();
				result = Integer.parseInt(rs.getString(1));
				return  result; 
			} catch (Exception e) {
				log.debug(sql);
				throw new Exception(e);
			}finally{
				if(null!=rs){
					rs.close();
				}if(null!=myPs){
					myPs.close();
				}
			}
		}
	}
	
	/**
	 * 聚合函数查询
	 * @param   必须的是一个 聚合函数    max  min count 
	 */
	public  int   getPrimaryKey(String  sql)throws Exception{
		if(TRANSACTION_ENTITY.get()==null){
			Connection myconn=DbcpConnection.getConnection();
			Statement myst = null;
			ResultSet rs=null;
			int  result = 0;
			try {
				myconn.setAutoCommit(true);
				myst = myconn.createStatement();
				rs  = myst.executeQuery(sql);
				rs.next();
			    result = Integer.parseInt(rs.getString(1));
				return  result;
			} catch(Exception e){
				log.debug(sql);
				throw new Exception(e);
			}finally{
				if(null!=rs){
					rs.close();
				}if(null!=myst){
					myst.close();
				}if(null!=myconn){
					myconn.close();
				}
			}	
		}else{
			Statement myst = TRANSACTION_ENTITY.get().getConn().createStatement();
			ResultSet rs=null;
			int  result = 0;
			try {
				rs  = myst.executeQuery(sql);
				rs.next();
				result = Integer.parseInt(rs.getString(1));
				return  result;
			} catch (Exception e) {
				log.debug(sql);
				throw new Exception(e);
			}finally{
				if(null!=rs){
					rs.close();
				}if(null!=myst){
					myst.close();
				}
			}			
		}
	}
	
	/**
	 * 预处理查询
	 * @param sql
	 * @param map
	 * @return
	 * @throws Exception
	 */
	public List<Map<String, Object>> query(String sql,Map map)throws Exception{
		Map map2 = sqlAnalyze2(sql, map);
		return psQuery((String)map2.get("sql"),(List)map2.get("list"));
	} 
	
	/**
	 * 预处理查询
	 * @param sql
	 * @param params
	 * @return
	 * @throws Exception
	 */
	private List<Map<String, Object>> psQuery(String sql,List params)throws Exception{
		if(TRANSACTION_ENTITY.get()==null){
			Connection myconn=DbcpConnection.getConnection();	
			PreparedStatement myPs=null;
			ResultSet rs=null;
			try {
				myconn.setAutoCommit(true);
				myPs = myconn.prepareStatement(sql);
				if(params != null && params.size() > 0){
					for (int i = 0; i < params.size(); i++) {
						myPs.setString(i+1,(String) params.get(i));
					}
				}
				 rs = myPs.executeQuery();
				 return getList(rs);
			}catch(Exception e){
				log.debug(sql);
				log.debug(params);
				throw new Exception(e);
			}finally{
				if(null!=rs){
					rs.close();
				}if(null!=myPs){
					myPs.close();
				}if(null!=myconn){
					myconn.close();
				}
			}
		}else{
			
			PreparedStatement myPs=TRANSACTION_ENTITY.get().getConn().prepareStatement(sql);
			ResultSet rs=null;
			try {
				if(params != null && params.size() > 0){
					for (int i = 0; i < params.size(); i++) {
						myPs.setString(i+1,(String) params.get(i));
					}
				}
				rs = myPs.executeQuery();
				return getList(rs);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				log.debug(sql);
				log.debug(params);
				throw new Exception(e);
			}finally{
				if(null!=rs){
					rs.close();
				}if(null!=myPs){
					myPs.close();
				}
			}
		}
	}
	
	
	
	/**
	 * 普通查询
	 * @param sql
	 * @return
	 * @throws Exception
	 */
	public List<Map<String, Object>> query(String sql)throws Exception{
		if(TRANSACTION_ENTITY.get()==null){
			//未开启事务
			Connection myconn=DbcpConnection.getConnection();
			Statement myst =null;
			ResultSet rs=null;
			try {
				myconn.setAutoCommit(true);
				myst = myconn.createStatement();
				rs = myst.executeQuery(sql);
				return getList(rs);
			}catch(Exception e){
				log.debug(sql);
				throw new Exception(e);
			}finally{
				if(null!=rs){
					rs.close();
				}if(null!=myst){
					myst.close();
				}if(null!=myconn){
					myconn.close();
				}
			}
		}else{
			//事务开启
			Statement myst = TRANSACTION_ENTITY.get().getConn().createStatement();
			ResultSet rs=null;
			try {
				rs = myst.executeQuery(sql);
				return getList(rs);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				log.debug(sql);
				throw new Exception(e);
			}finally{
				if(null!=rs){
					rs.close();
				}if(null!=myst){
					myst.close();
				}
			}
			
		}
	}
	
	/**
	 * 普通处理
	 * @param sql
	 * @return
	 * @throws Exception
	 */
	public int update(String sql) throws Exception{
		if(TRANSACTION_ENTITY.get()==null){
			//未开启事务
			Connection myconn=DbcpConnection.getConnection();
			Statement myst=null;
			int i=0;
			try {
				myconn.setAutoCommit(true);

				myst = myconn.createStatement();
				i=myst.executeUpdate(sql);
			}catch(SQLException e) {
				log.debug(sql);
				throw new Exception(e);
			}finally{
				if(null!=myst){
					myst.close();
				}if(null!=myconn){
					myconn.close();
					
				}
			}
			return i;
		}else{
			Statement myst = TRANSACTION_ENTITY.get().getConn().createStatement();
			int i=0;
			try {
				i=myst.executeUpdate(sql);
			}catch(SQLException e) {
				log.debug(sql);
				throw new Exception(e);
			}finally{
				if(null!=myst){
					myst.close();
				}
			}
		}
		return 0;
	}
	
	
	/**
	 * 预处理
	 * @param sql
	 * @param map
	 * @return
	 * @throws Exception
	 */
	public int update(String sql,Map map)throws Exception{
		Map map2 = sqlAnalyze2(sql, map);
		return psUpdate((String)map2.get("sql"),(List)map2.get("list"));
	}
	
	/**
	 * 预处理
	 * @param sql
	 * @param params
	 * @return
	 * @throws Exception
	 */
	private int psUpdate(String sql,List params) throws Exception{
		if(TRANSACTION_ENTITY.get()==null){
			//未开启事务
			Connection myconn=DbcpConnection.getConnection();
			PreparedStatement myPs =null;
			try {
				myconn.setAutoCommit(true);

				myPs = myconn.prepareStatement(sql);
				if(params != null && params.size() > 0){
					for (int i = 0; i < params.size(); i++) {
						myPs.setString(i+1,(String) params.get(i));
					}
				}
				myPs.executeUpdate();
			} catch (Exception e) {
				log.debug(sql);
				log.debug(params);
				// TODO Auto-generated catch block
				throw new Exception(e);
			}finally{
				if(null!=myPs){
					myPs.close();
				}if(null!=myconn){
					myconn.close();
				}
			}
		}else{
			//开启了事务
			PreparedStatement myPs = TRANSACTION_ENTITY.get().getConn().prepareStatement(sql);
			try {
				if(params != null && params.size() > 0){
					for (int i = 0; i < params.size(); i++) {
						myPs.setString(i+1,(String) params.get(i));
					}
				}
				myPs.executeUpdate();
			} catch (Exception e) {
				log.debug(sql);
				log.debug(params);
				// TODO Auto-generated catch block
				throw new Exception(e);
			}finally{
				if(null!=myPs){
					myPs.close();
				}
			}
		}
		return 0;		
	}
	
	public Map<String, Object> queryForMap(String  sql) throws Exception{
		List<Map<String, Object>> list =query(sql);
		if(list.size()>1){
			log.debug(sql);
			throw new Exception("方法queryForMap返回了多条数据");
		}
		if(list.size()==0){
			return null;
		}
		return list.get(0);
	}
	public Map<String, Object> queryForMap(String  sql,Map map) throws Exception{
		List<Map<String, Object>> list =query(sql,map);
		if(list.size()>1){
			log.debug(sql);
			log.debug(map);
			throw new Exception("方法queryForMap返回了多条数据");
		}
		if(list.size()==0){
			return null;
		}
		return list.get(0);
	}
	public Object queryForOne(String  sql,Map map) throws Exception{
		List<Map<String, Object>> list =query(sql,map);
		if(list.size()<=0){
			log.debug(sql);
			log.debug(map);
			return null;
		}
		if(list.size()>1){
			log.debug(sql);
			log.debug(map);
			throw new Exception("方法queryForOne返回了多条数据");
		}
		Map<String, Object> reMap = (Map<String, Object>) list.get(0);
		if(reMap.size()==1){
			for (Object v : reMap.values()) {
				return v;
			}
		}else if(reMap.size()>1){
			log.debug(sql);
			log.debug(map);
			throw new Exception("方法queryForOne返回了多列数据");
		}
		return null;
	}
	public Object queryForOne(String  sql) throws Exception{
		List<Map<String, Object>> list =query(sql);
		if(list.size()<=0){
			log.debug(sql);
			return null;
		}
		if(list.size()>1){
			log.debug(sql);
			throw new Exception("方法queryForOne返回了多条数据");
		}
		Map<String, Object> reMap = (Map<String, Object>) list.get(0);
		if(reMap.size()==1){
			for (Object v : reMap.values()) {
				return v;
			}
		}else if(reMap.size()>1){
			log.debug(sql);
			throw new Exception("方法queryForOne返回了多列数据");
		}
		return null;
	}
}
